﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace OfflineModemCheck
{
    public class SQLiteMethod
    {
        /// <summary>
        /// 获取一个SQLitle连接，兼顾创建一个DB
        /// </summary>
        /// <param name="dictoryPath"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static SQLiteConnection ConnectionSQLite(string dictoryPath, string fileName)
        {
            try
            {
                if (Directory.Exists(dictoryPath) == false)
                {
                    Directory.CreateDirectory(dictoryPath);
                }

                string dbPath = dictoryPath + fileName;

                SQLiteConnection cn = new SQLiteConnection(string.Format("data source=" + dbPath));
                cn.Open();
                cn.Close();
                return cn;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 删除DB
        /// </summary>
        /// <param name="dbPath"></param>
        public static void DeleteDB(string dbPath)
        {
            if (File.Exists(dbPath))
            {
                File.Delete(dbPath);
            }
        }

        /// <summary>
        /// 执行SQL语句,UPDATE,DELETE,INSERT,CREATE TABLE,DROP TABLE
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecSQL(SQLiteConnection connection, string sql, SQLiteParameter[] parameters = null)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                int ret = cmd.ExecuteNonQuery();

                cmd.Dispose();

                connection.Close();

                return ret;
            }
            catch
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<string> ReaderTableColumns(SQLiteConnection connection,string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                SQLiteDataReader reader = cmd.ExecuteReader();

                List<string> listCol = new List<string>();

                while (reader.Read())
                {
                    for(int i = 0; i < reader.FieldCount; i++)
                    {
                        listCol.Add($"{ reader[i]}");
                    }
                }

                reader.Close();
                connection.Close();

                return listCol;
            }
            catch
            {
                return null;
            }
        }

        public static DataTable ReaderTableColumnsTable(SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                SQLiteDataReader reader = cmd.ExecuteReader();

                DataTable dataTable = new DataTable();

                while (reader.Read())
                {
                    dataTable.Columns.Add(new DataColumn($"{ reader[1].ToString()}"));
                }

                DataRow row = dataTable.NewRow();
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    row[i] = dataTable.Columns[i].ColumnName;
                }

                dataTable.Rows.Add(row);

                reader.Close();
                connection.Close();

                return dataTable;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 读取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> ExecReader<T>(SQLiteConnection connection, string sql, SQLiteParameter[] parameters) where T : struct
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }


                List<T> ts = new List<T>();

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {

                    FieldInfo[] fieldList = typeof(T).GetFields();

                    while (reader.Read())
                    {
                        T data=new T();
                        for (int i = 0; i < fieldList.Length; i++)
                        {
                            try
                            {
                                if (reader[fieldList[i].Name] == DBNull.Value)
                                {
                                    fieldList[i].SetValue(data, default(T));
                                }
                                else
                                {
                                    fieldList[i].SetValue(data, reader[fieldList[i].Name]);
                                }
                            }
                            catch
                            {
                                fieldList[i].SetValue(data, default(T));
                            }
                        }
                        ts.Add(data);
                    }
                }

                cmd.Dispose();
                connection.Close();

                return ts;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取第一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T GetFirstCell<T>(SQLiteConnection connection,string sql,SQLiteParameter[] parameters)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                T cell = default(T);

                var ret = cmd.ExecuteScalar();

                if (ret != DBNull.Value) cell = (T)ret;

                cmd.Dispose();
                connection.Close();

                return cell;
            }
            catch
            {
                return default(T);
            }
        }

        public static event Action<string> OnChange;

        public static bool ReplaceFromDataTable(SQLiteConnection connection, DataTable dt, string tableName)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection
                };

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string columns = string.Empty;
                    string values = string.Empty;
                    //SQLiteParameter[] parameters = new SQLiteParameter[dt.Columns.Count];
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        columns += string.Format("{0},", dt.Columns[j].ColumnName);
                        //values += string.Format("@U{0},", j);
                        //parameters[j] = new SQLiteParameter($"@U{j}", dt.Rows[i][j] ?? DBNull.Value);

                        if (dt.Rows[i][j] == null)
                        {
                            values += "NULL";
                        }
                        else
                        {
                            values += (typeof(string) == dt.Columns[j].DataType||typeof(DateTime)==dt.Columns[j].DataType 
                                ? $"'{dt.Rows[i][j]}'" : dt.Rows[i][j]);
                            values += ",";
                        }

                    }

                    string sqlStr = string.Format("INSERT  INTO {0} ( {1} ) VALUES ( {2} ) ", tableName, columns.TrimEnd(','), values.TrimEnd(','));

                    cmd.CommandText = sqlStr;
                    //cmd.Parameters.AddRange(parameters);

                    var result = cmd.ExecuteNonQuery();

                    if (result >= 1) OnChange?.Invoke($"INSERT:{i}");
                    else OnChange?.Invoke($"INSERT FAIL:{i}");
                }

                return true;
            }
            catch(Exception ex)
            {
                return false;
            }
        }
    }
}
